/**
 * @name         RepNET Common methods 
 * @version      1.0a
 * @author       Kasun Rathnayaka <kazar55@gmail.com>
 * @author       Ramindu Deshapriya <rasade88@gmail.com>
 * @author 		 Thusira Dissanayake <thusira14@gmail.com>
 * @about        Developed by MiDi
 * @license	 http://www.gnu.org/licenses/lgpl-2.1.html GNU Lesser General Public License (LGPL)
 * @lastModified 2012.09.24
 */
package com.midi.repnet.main;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.midi.repnet.models.*;
import com.mysql.jdbc.PreparedStatement;

public class Common {
	public static ArrayList<Shop> shops = null;
	public static ArrayList<String> bills = null;
	private static ResultSet billNoResult;
	private static ResultSet agencyResult;
	private static ResultSet transactionResult;
	/**
	 * Method to return list of agency names to populate combo box
	 * @return <strong>ArrayList</strong> List of agency names from the database
	 */
	public static ArrayList<Agency> loadAgencies() {
		ArrayList<Agency> agencies = new ArrayList<Agency>();
		try {
			Statement statement = Main.dbConnection.createStatement();
			ResultSet res = statement.executeQuery("SELECT * FROM agency");
			while ( res.next() ) {
				agencies.add(new Agency(res.getInt("id"),res.getString("name")));
			}
		}
		catch ( Exception e ) {
			e.printStackTrace();
		}
		return agencies;
	}
	/**
	 * Method to return list of region names to populate combo box
	 * @return <strong>ArrayList</strong> List of region names from the database
	 */
	public static ArrayList<Region> loadRegions() {
		ArrayList<Region> regions = new ArrayList<Region>();
		try {
			Statement statement = Main.dbConnection.createStatement();
			ResultSet res = statement.executeQuery("SELECT * FROM regions");
			while ( res.next() ) {
				//if(!res.getString("name").equals("Uncategorized")){
					regions.add(new Region(res.getInt("id"),res.getString("name")));
				//}				
			}
		}
		catch ( Exception e ) {
			e.printStackTrace();
		}
		return regions;
	}
	/**
	 * Method to return list of region names to populate combo box
	 * @param region selected region in Region combo box
	 * @return
	 */
	public static ArrayList<Shop> loadShops(String region) {
		ArrayList<Shop> shops = new ArrayList<Shop>();
		try {
			PreparedStatement statement = (PreparedStatement)Main.dbConnection.prepareStatement("SELECT s.id as id,s.region_id as region_id,s.name as name,s.address as address,s.phone as phone,r.name as region_name FROM shops as s LEFT JOIN regions as r ON s.region_id=r.id WHERE r.name LIKE ?");
			statement.setString(1, region);
			ResultSet res = statement.executeQuery();
			while ( res.next() ) {
				shops.add(new Shop(res.getInt("id"),res.getInt("region_id"),res.getString("name"),res.getString("address"),res.getString("phone"),new Region(res.getInt("region_id"),res.getString("region_name"))));
			}
		}
		catch ( Exception e ) {
			e.printStackTrace();
		}
		return shops;
	}
	public static ArrayList<String> loadBills(String shop, String agency) {
		ArrayList<String> bills = new ArrayList<String>();
		try {
			PreparedStatement statement1 = (PreparedStatement)Main.dbConnection.prepareStatement("SELECT t.bill_no, t.agency_id FROM transactions as t LEFT JOIN shops as s ON s.id=t.shop_id WHERE s.name LIKE ?");
			statement1.setString(1, shop);
			PreparedStatement statement2 = (PreparedStatement)Main.dbConnection.prepareStatement("SELECT id FROM agency WHERE name=?");
			statement2.setString(1, agency);
			billNoResult = statement1.executeQuery();
			agencyResult = statement2.executeQuery();
			if ( agencyResult.next() ) {
				//an agency was selected, load bill no's belonging to this agency
				String agencyId = agencyResult.getString("id");
				while ( billNoResult.next() ) {
					if ( billNoResult.getString("agency_id").equals(agencyId) ) {
						bills.add(billNoResult.getString("bill_no"));
					}
					//ResultSet res2 = statement.executeQuery("SELECT t.bill_no FROM transactions as t LEFT JOIN agency as a ON a.id=t.agency_id WHERE a.name LIKE '"+agency+"' AND t.bill_no='"+billno+"'");
				}
			}
			else {
				while ( billNoResult.next() ) {
					bills.add(billNoResult.getString("bill_no"));
				}
			}
			
		}
		catch ( Exception e ) {
			e.printStackTrace();
		}
		return bills;
	}
	/**
	 * Call this method when deleting an agency from the database. 
	 * If the method returns <strong>false</strong>, this means there are still unclosed transactions remaining
	 * which are associated with that agency. In this case, display a message box saying 'Unclosed transactions for 
	 * agency, could not delete'
	 * @param agencyname The name of the agency being deleted
	 * @return
	 */
	public static boolean updateTransactionsOnAgencyDelete(String agencyname) {
		try {
			PreparedStatement transactionQuery = (PreparedStatement)Main.dbConnection.prepareStatement("SELECT * FROM transactions as t INNER JOIN agency as a ON a.id=t.agency_id WHERE a.name=?");
			transactionQuery.setString(1, agencyname);
			transactionResult = transactionQuery.executeQuery();
			while ( transactionResult.next() ) {
				float remainder = transactionResult.getFloat("remainder");
				//System.out.println("Selected billno: "+transactionResult.getString("bill_no"));
				if ( remainder > 0 ) {
					return false;
				}
			}
			PreparedStatement agencyIdQuery = (PreparedStatement)Main.dbConnection.prepareStatement("SELECT id FROM agency WHERE name=?");
			agencyIdQuery.setString(1, agencyname);
			ResultSet agencyIdResult = agencyIdQuery.executeQuery();
			agencyIdResult.next();
			int agencyId = agencyIdResult.getInt("id");
			Statement deleteQuery = Main.dbConnection.createStatement();
			if ( deleteQuery.executeUpdate("DELETE FROM transactions WHERE agency_id='"+agencyId+"'") == 0 ) {
				return false;
			}
			
		}
		catch (SQLException sqle) {
			sqle.printStackTrace();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		return true;
	}
	/**
	 * Call this method when deleting an agency from the database. 
	 * If the method returns <strong>false</strong>, this means there are still unclosed transactions remaining
	 * which are associated with that shop. In this case, display a message box saying 'Unclosed transactions for 
	 * shop, could not delete'
	 * @param shopname the name of the shop that is being deleted
	 * @return boolean value to show whether that particular shop can be deleted
	 */
	public static boolean updateTransactionsOnShopDelete(String shopname) {
		try {
			PreparedStatement transactionQuery = (PreparedStatement)Main.dbConnection.prepareStatement("SELECT * FROM transactions as t INNER JOIN shops as s ON s.id=t.shop_id WHERE s.name=?");
			transactionQuery.setString(1, shopname);
			transactionResult = transactionQuery.executeQuery();
			while ( transactionResult.next() ) {
				float remainder = transactionResult.getFloat("remainder");
				//System.out.println("Selected billno: "+transactionResult.getString("bill_no"));
				if ( remainder > 0 ) {
					return false;
				}
			}
			PreparedStatement shopIdQuery = (PreparedStatement)Main.dbConnection.prepareStatement("SELECT id FROM shops WHERE name=?");
			shopIdQuery.setString(1, shopname);
			ResultSet shopIdResult = shopIdQuery.executeQuery();
			shopIdResult.next();
			int shopId = shopIdResult.getInt("id");
			Statement deleteQuery = Main.dbConnection.createStatement();
			if ( deleteQuery.executeUpdate("DELETE FROM transactions WHERE shop_id='"+shopId+"'") == 0 ) {
				return false;
			}
			
		}
		catch (SQLException sqle) {
			sqle.printStackTrace();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		return true;
	}
	
}
